{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/tmp/pycharm_project_293/reco_sys\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import sys\n",
    "# 如果当前代码文件运行测试需要加入修改路径，避免出现后导包问题\n",
    "BASE_DIR = os.path.dirname(os.path.dirname(os.getcwd()))\n",
    "sys.path.insert(0, os.path.join(BASE_DIR))\n",
    "print(BASE_DIR)\n",
    "PYSPARK_PYTHON = \"/root/anaconda3/envs/toutiao/bin/python\"\n",
    "# 当存在多个版本时，不指定很可能会导致出错\n",
    "os.environ[\"PYSPARK_PYTHON\"] = PYSPARK_PYTHON\n",
    "os.environ[\"PYSPARK_DRIVER_PYTHON\"] = PYSPARK_PYTHON\n",
    "from offline import SparkSessionBase"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "class OriginArticleData(SparkSessionBase):\n",
    "    \n",
    "    SPARK_APP_NAME = \"mergeArticle\"\n",
    "    SPARK_URL = \"local\"\n",
    "\n",
    "    ENABLE_HIVE_SUPPORT = True\n",
    "    \n",
    "    def __init__(self):\n",
    "        self.spark = self._create_spark_session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/01/23 11:46:13 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "oa = OriginArticleData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/01/23 11:46:21 WARN HiveConf: HiveConf of name hive.metastore.event.db.notification.api.auth does not exist\n"
     ]
    }
   ],
   "source": [
    "# 进行文章 前两个表 的合并\n",
    "oa.spark.sql(\"use toutiao\")\n",
    "# news_article_basic 与news_article_content, article_id\n",
    "title_content = oa.spark.sql(\"select a.article_id, a.channel_id, a.title, b.content from news_article_basic a inner join news_article_content b on a.article_id=b.article_id\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-------------------------------------+----------------------------------+\n",
      "|         article_id|         channel_id|                                title|                           content|\n",
      "+-------------------+-------------------+-------------------------------------+----------------------------------+\n",
      "|1737804352460038144|1737804352195797007|     荣耀10疑似29日发布 联合阿迪推...|      <div><p>【手机中国 新闻】...|\n",
      "|1737804352468426752|1737804352195797004|用语音控制解放双手，只是海尔卫玺智...|             <div><p><strong>［...|\n",
      "|1737804352472621056|1737804352195797032|京东六六案：重要证据被处理还是搜索...|   <p>315前后，由于淘宝偏袒卖家...|\n",
      "|1737804352476815360|1737804352195797043|      百科丨解读HDR火热背后的技术优势|<p>目前不但家用发烧投影设备，就...|\n",
      "|1737804352485203968|1737804352195797004|       吴亦凡代言！小米MIX 2S官方自曝|              <div><p><img alt=...|\n",
      "|1737804352489398272|1737804352195797026|  谷歌AI图像分层算法代码曝光，你看...|     <div><p>技术改变生活，科学...|\n",
      "|1737804352493592576|1737804352199991296|  驾呗共享汽车：打造“三网一位”智能...|       <img alt=\"驾呗共享汽车：...|\n",
      "|1737804352497786880|1737804352195797044|      APP注册设计二三事：关于APP注...|             <blockquote><p>对A...|\n",
      "|1737804352506175488|1737804352199991305|亮屏后屏占比惊人！小米机皇真机上手...|     <div><p>目前，小米新年第一...|\n",
      "|1737804352514564096|1737804352195797039|       雷军自曝小米MIX 2S真机图 屏...|      <div><p>【手机中国 新闻】...|\n",
      "|1737804352518758400|1737804352195797002|芝麻信用：押金本不该变成负担，呼吁...|     <div><p>去年多家共享单车被...|\n",
      "|1737804352522952704|1737804352195797024|为小米上市开路！雷军已辞去多个职务...|     <div><p>小米上市的消息越来...|\n",
      "|1737804352527147008|1737804352195797028|      人工智能焕新生活 三星Bixby视...|            <p><strong>三星Bixb...|\n",
      "|1737804352535535616|1737804352195796994|    先发优势明显！上海长宁区12家AI...|              <div><p><img alt=...|\n",
      "|1737804352539729920|1737804352195797029|  中方资本、美国成立、造车新势力SF...|              <div><p><img alt=...|\n",
      "|1737804352543924224|1737804352199991307|  苹果全家桶的最强搭配！多灵P8智能...|     <div><p>随着科技的发展，如...|\n",
      "|1737804352548118528|1737804352199991303|区块链安全性果真无懈可击？九大漏洞...|         <div><p>欧界报道：</p>...|\n",
      "|1737804352552312832|1737804352199991307|        今晚vivo X21发布会看点汇总...|           <div><p>今晚19：30 v...|\n",
      "|1737804352556507136|1737804352195797012|把吴恩达深度学习系列课程画出来，这...|              <div><p><img alt=...|\n",
      "|1737804352564895744|1737804352195797023|       基于Docker技术的超级计算云平台|     <div><p>云计算的发展速度已...|\n",
      "+-------------------+-------------------+-------------------------------------+----------------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "title_content.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/root/anaconda3/envs/toutiao/lib/python3.9/site-packages/pyspark/sql/dataframe.py:330: FutureWarning: Deprecated in 2.0, use createOrReplaceTempView instead.\n",
      "  warnings.warn(\"Deprecated in 2.0, use createOrReplaceTempView instead.\", FutureWarning)\n"
     ]
    }
   ],
   "source": [
    "# 进行title_content 与 文章频道名称合并\n",
    "title_content.registerTempTable('temptable')\n",
    "\n",
    "channel_title_content = oa.spark.sql(\"select t.*, n.channel_name from temptable t left join news_channel n on t.channel_id=n.channel_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-------------------------------------+----------------------------------+------------+\n",
      "|         article_id|         channel_id|                                title|                           content|channel_name|\n",
      "+-------------------+-------------------+-------------------------------------+----------------------------------+------------+\n",
      "|1737804352460038144|1737804352195797007|     荣耀10疑似29日发布 联合阿迪推...|      <div><p>【手机中国 新闻】...|        null|\n",
      "|1737804352468426752|1737804352195797004|用语音控制解放双手，只是海尔卫玺智...|             <div><p><strong>［...|        null|\n",
      "|1737804352472621056|1737804352195797032|京东六六案：重要证据被处理还是搜索...|   <p>315前后，由于淘宝偏袒卖家...|        null|\n",
      "|1737804352476815360|1737804352195797043|      百科丨解读HDR火热背后的技术优势|<p>目前不但家用发烧投影设备，就...|        null|\n",
      "|1737804352485203968|1737804352195797004|       吴亦凡代言！小米MIX 2S官方自曝|              <div><p><img alt=...|        null|\n",
      "|1737804352489398272|1737804352195797026|  谷歌AI图像分层算法代码曝光，你看...|     <div><p>技术改变生活，科学...|        null|\n",
      "|1737804352493592576|1737804352199991296|  驾呗共享汽车：打造“三网一位”智能...|       <img alt=\"驾呗共享汽车：...|        null|\n",
      "|1737804352497786880|1737804352195797044|      APP注册设计二三事：关于APP注...|             <blockquote><p>对A...|        null|\n",
      "|1737804352506175488|1737804352199991305|亮屏后屏占比惊人！小米机皇真机上手...|     <div><p>目前，小米新年第一...|        null|\n",
      "|1737804352514564096|1737804352195797039|       雷军自曝小米MIX 2S真机图 屏...|      <div><p>【手机中国 新闻】...|        null|\n",
      "|1737804352518758400|1737804352195797002|芝麻信用：押金本不该变成负担，呼吁...|     <div><p>去年多家共享单车被...|        null|\n",
      "|1737804352522952704|1737804352195797024|为小米上市开路！雷军已辞去多个职务...|     <div><p>小米上市的消息越来...|        null|\n",
      "|1737804352527147008|1737804352195797028|      人工智能焕新生活 三星Bixby视...|            <p><strong>三星Bixb...|        null|\n",
      "|1737804352535535616|1737804352195796994|    先发优势明显！上海长宁区12家AI...|              <div><p><img alt=...|        null|\n",
      "|1737804352539729920|1737804352195797029|  中方资本、美国成立、造车新势力SF...|              <div><p><img alt=...|        null|\n",
      "|1737804352543924224|1737804352199991307|  苹果全家桶的最强搭配！多灵P8智能...|     <div><p>随着科技的发展，如...|        null|\n",
      "|1737804352548118528|1737804352199991303|区块链安全性果真无懈可击？九大漏洞...|         <div><p>欧界报道：</p>...|        null|\n",
      "|1737804352552312832|1737804352199991307|        今晚vivo X21发布会看点汇总...|           <div><p>今晚19：30 v...|        null|\n",
      "|1737804352556507136|1737804352195797012|把吴恩达深度学习系列课程画出来，这...|              <div><p><img alt=...|        null|\n",
      "|1737804352564895744|1737804352195797023|       基于Docker技术的超级计算云平台|     <div><p>云计算的发展速度已...|        null|\n",
      "+-------------------+-------------------+-------------------------------------+----------------------------------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "channel_title_content.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并三个内容到一个字符串\n",
    "import pyspark.sql.functions as F\n",
    "\n",
    "sentence_df = channel_title_content.select(\"article_id\", \"channel_id\", \"channel_name\", \"title\", \"content\", \n",
    "                            F.concat_ws(',', \n",
    "                                       channel_title_content.channel_name,\n",
    "                                       channel_title_content.title,\n",
    "                                       channel_title_content.content).alias('sentence'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "|         article_id|         channel_id|channel_name|                                title|                           content|                             sentence|\n",
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "|1737804352460038144|1737804352195797007|        null|     荣耀10疑似29日发布 联合阿迪推...|      <div><p>【手机中国 新闻】...|     荣耀10疑似29日发布 联合阿迪推...|\n",
      "|1737804352468426752|1737804352195797004|        null|用语音控制解放双手，只是海尔卫玺智...|             <div><p><strong>［...|用语音控制解放双手，只是海尔卫玺智...|\n",
      "|1737804352472621056|1737804352195797032|        null|京东六六案：重要证据被处理还是搜索...|   <p>315前后，由于淘宝偏袒卖家...|京东六六案：重要证据被处理还是搜索...|\n",
      "|1737804352476815360|1737804352195797043|        null|      百科丨解读HDR火热背后的技术优势|<p>目前不但家用发烧投影设备，就...|   百科丨解读HDR火热背后的技术优势...|\n",
      "|1737804352485203968|1737804352195797004|        null|       吴亦凡代言！小米MIX 2S官方自曝|              <div><p><img alt=...|      吴亦凡代言！小米MIX 2S官方自...|\n",
      "|1737804352489398272|1737804352195797026|        null|  谷歌AI图像分层算法代码曝光，你看...|     <div><p>技术改变生活，科学...|  谷歌AI图像分层算法代码曝光，你看...|\n",
      "|1737804352493592576|1737804352199991296|        null|  驾呗共享汽车：打造“三网一位”智能...|       <img alt=\"驾呗共享汽车：...|  驾呗共享汽车：打造“三网一位”智能...|\n",
      "|1737804352497786880|1737804352195797044|        null|      APP注册设计二三事：关于APP注...|             <blockquote><p>对A...|      APP注册设计二三事：关于APP注...|\n",
      "|1737804352506175488|1737804352199991305|        null|亮屏后屏占比惊人！小米机皇真机上手...|     <div><p>目前，小米新年第一...|亮屏后屏占比惊人！小米机皇真机上手...|\n",
      "|1737804352514564096|1737804352195797039|        null|       雷军自曝小米MIX 2S真机图 屏...|      <div><p>【手机中国 新闻】...|       雷军自曝小米MIX 2S真机图 屏...|\n",
      "|1737804352518758400|1737804352195797002|        null|芝麻信用：押金本不该变成负担，呼吁...|     <div><p>去年多家共享单车被...|芝麻信用：押金本不该变成负担，呼吁...|\n",
      "|1737804352522952704|1737804352195797024|        null|为小米上市开路！雷军已辞去多个职务...|     <div><p>小米上市的消息越来...|为小米上市开路！雷军已辞去多个职务...|\n",
      "|1737804352527147008|1737804352195797028|        null|      人工智能焕新生活 三星Bixby视...|            <p><strong>三星Bixb...|      人工智能焕新生活 三星Bixby视...|\n",
      "|1737804352535535616|1737804352195796994|        null|    先发优势明显！上海长宁区12家AI...|              <div><p><img alt=...|    先发优势明显！上海长宁区12家AI...|\n",
      "|1737804352539729920|1737804352195797029|        null|  中方资本、美国成立、造车新势力SF...|              <div><p><img alt=...|  中方资本、美国成立、造车新势力SF...|\n",
      "|1737804352543924224|1737804352199991307|        null|  苹果全家桶的最强搭配！多灵P8智能...|     <div><p>随着科技的发展，如...|  苹果全家桶的最强搭配！多灵P8智能...|\n",
      "|1737804352548118528|1737804352199991303|        null|区块链安全性果真无懈可击？九大漏洞...|         <div><p>欧界报道：</p>...|区块链安全性果真无懈可击？九大漏洞...|\n",
      "|1737804352552312832|1737804352199991307|        null|        今晚vivo X21发布会看点汇总...|           <div><p>今晚19：30 v...|        今晚vivo X21发布会看点汇总...|\n",
      "|1737804352556507136|1737804352195797012|        null|把吴恩达深度学习系列课程画出来，这...|              <div><p><img alt=...|把吴恩达深度学习系列课程画出来，这...|\n",
      "|1737804352564895744|1737804352195797023|        null|       基于Docker技术的超级计算云平台|     <div><p>云计算的发展速度已...|      基于Docker技术的超级计算云平...|\n",
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sentence_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "|         article_id|         channel_id|channel_name|                                title|                           content|                             sentence|\n",
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "|1737804352460038144|1737804352195797007|        null|     荣耀10疑似29日发布 联合阿迪推...|      <div><p>【手机中国 新闻】...|     荣耀10疑似29日发布 联合阿迪推...|\n",
      "|1737804352468426752|1737804352195797004|        null|用语音控制解放双手，只是海尔卫玺智...|             <div><p><strong>［...|用语音控制解放双手，只是海尔卫玺智...|\n",
      "|1737804352472621056|1737804352195797032|        null|京东六六案：重要证据被处理还是搜索...|   <p>315前后，由于淘宝偏袒卖家...|京东六六案：重要证据被处理还是搜索...|\n",
      "|1737804352476815360|1737804352195797043|        null|      百科丨解读HDR火热背后的技术优势|<p>目前不但家用发烧投影设备，就...|   百科丨解读HDR火热背后的技术优势...|\n",
      "|1737804352485203968|1737804352195797004|        null|       吴亦凡代言！小米MIX 2S官方自曝|              <div><p><img alt=...|      吴亦凡代言！小米MIX 2S官方自...|\n",
      "|1737804352489398272|1737804352195797026|        null|  谷歌AI图像分层算法代码曝光，你看...|     <div><p>技术改变生活，科学...|  谷歌AI图像分层算法代码曝光，你看...|\n",
      "|1737804352493592576|1737804352199991296|        null|  驾呗共享汽车：打造“三网一位”智能...|       <img alt=\"驾呗共享汽车：...|  驾呗共享汽车：打造“三网一位”智能...|\n",
      "|1737804352497786880|1737804352195797044|        null|      APP注册设计二三事：关于APP注...|             <blockquote><p>对A...|      APP注册设计二三事：关于APP注...|\n",
      "|1737804352506175488|1737804352199991305|        null|亮屏后屏占比惊人！小米机皇真机上手...|     <div><p>目前，小米新年第一...|亮屏后屏占比惊人！小米机皇真机上手...|\n",
      "|1737804352514564096|1737804352195797039|        null|       雷军自曝小米MIX 2S真机图 屏...|      <div><p>【手机中国 新闻】...|       雷军自曝小米MIX 2S真机图 屏...|\n",
      "+-------------------+-------------------+------------+-------------------------------------+----------------------------------+-------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 读取文章，进行每篇张分词\n",
    "oa.spark.sql(\"use article\")\n",
    "article_data = oa.spark.sql(\"select * from article_data limit 10\")\n",
    "article_data.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 文章数据进行分词处理,得到分词结果\n",
    "# 分词\n",
    "def segmentation(partition):\n",
    "    import os\n",
    "    import re\n",
    "\n",
    "    import jieba\n",
    "    import jieba.analyse\n",
    "    import jieba.posseg as pseg\n",
    "    import codecs\n",
    "\n",
    "    abspath = \"/export/server/toutiao/words\"\n",
    "\n",
    "    # 结巴加载用户词典\n",
    "    userDict_path = os.path.join(abspath, \"ITKeywords.txt\")\n",
    "    jieba.load_userdict(userDict_path)\n",
    "\n",
    "    # 停用词文本\n",
    "    stopwords_path = os.path.join(abspath, \"stop_words.txt\")\n",
    "\n",
    "    def get_stopwords_list():\n",
    "        \"\"\"返回stopwords列表\"\"\"\n",
    "        stopwords_list = [i.strip()\n",
    "                          for i in codecs.open(stopwords_path).readlines()]\n",
    "        return stopwords_list\n",
    "\n",
    "    # 所有的停用词列表\n",
    "    stopwords_list = get_stopwords_list()\n",
    "\n",
    "    # 分词\n",
    "    def cut_sentence(sentence):\n",
    "        \"\"\"对切割之后的词语进行过滤，去除停用词，保留名词，英文和自定义词库中的词，长度大于2的词\"\"\"\n",
    "        # print(sentence,\"*\"*100)\n",
    "        # eg:[pair('今天', 't'), pair('有', 'd'), pair('雾', 'n'), pair('霾', 'g')]\n",
    "        seg_list = pseg.lcut(sentence)\n",
    "        seg_list = [i for i in seg_list if i.flag not in stopwords_list]\n",
    "        filtered_words_list = []\n",
    "        for seg in seg_list:\n",
    "            # print(seg)\n",
    "            if len(seg.word) <= 1:\n",
    "                continue\n",
    "            elif seg.flag == \"eng\":\n",
    "                if len(seg.word) <= 2:\n",
    "                    continue\n",
    "                else:\n",
    "                    filtered_words_list.append(seg.word)\n",
    "            elif seg.flag.startswith(\"n\"):\n",
    "                filtered_words_list.append(seg.word)\n",
    "            elif seg.flag in [\"x\", \"eng\"]:  # 是自定一个词语或者是英文单词\n",
    "                filtered_words_list.append(seg.word)\n",
    "        return filtered_words_list\n",
    "\n",
    "    for row in partition:\n",
    "        sentence = re.sub(\"<.*?>\", \"\", row.sentence)    # 替换掉标签数据\n",
    "        words = cut_sentence(sentence)\n",
    "        yield row.article_id, row.channel_id, words\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.818 seconds.\n",
      "Prefix dict has been built successfully.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "words_df = article_data.rdd.mapPartitions(segmentation).toDF(['article_id', 'channel_id', 'words'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.816 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------------+\n",
      "|         article_id|         channel_id|                       words|\n",
      "+-------------------+-------------------+----------------------------+\n",
      "|1737804352460038144|1737804352195797007|[荣耀, 阿迪, 功能, 手机, ...|\n",
      "|1737804352468426752|1737804352195797004|[语音, 双手, 海尔, 卫玺, ...|\n",
      "|1737804352472621056|1737804352195797032|[京东, 证据, 方法, 商家, ...|\n",
      "|1737804352476815360|1737804352195797043|  [百科, HDR, 火热, 技术,...|\n",
      "|1737804352485203968|1737804352195797004| [吴亦凡, 代言, 小米, MIX...|\n",
      "|1737804352489398272|1737804352195797026|[谷歌, 图像, 分层, 算法, ...|\n",
      "|1737804352493592576|1737804352199991296|[汽车, 三网, 智能, 平台, ...|\n",
      "|1737804352497786880|1737804352195797044|      [APP, APP, 总结, AP...|\n",
      "|1737804352506175488|1737804352199991305|[亮屏, 小米, 机皇, 机上, ...|\n",
      "|1737804352514564096|1737804352195797039|  [雷军, 小米, MIX, 真机,...|\n",
      "+-------------------+-------------------+----------------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "words_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 先计算分词之后的每篇文章的词频，得到CV模型\n",
    "# 统计所有文章不同的词，组成一个词列表 words_list = [1,2,3,,34,4,45,56,67,78,8.......,,,,.]\n",
    "from pyspark.ml.feature import CountVectorizer\n",
    "cv = CountVectorizer(inputCol='words', outputCol='countFeatures', vocabSize=2000, minDF=1.0)\n",
    "cv_model = cv.fit(words_df)\n",
    "\n",
    "# 然后根据词频计算IDF以及词，得到IDF模型\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "cv_model.write().overwrite().save(\"hdfs://node1:8020/headlines/models/test.model\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.ml.feature import CountVectorizerModel\n",
    "cv_m = CountVectorizerModel.load(\"hdfs://node1:8020/headlines/models/test.model\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "cv_result = cv_m.transform(words_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "|         article_id|         channel_id|                       words|       countFeatures|\n",
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "|1737804352460038144|1737804352195797007|[荣耀, 阿迪, 功能, 手机, ...|(642,[0,3,9,10,11...|\n",
      "|1737804352468426752|1737804352195797004|[语音, 双手, 海尔, 卫玺, ...|(642,[0,3,4,5,6,8...|\n",
      "|1737804352472621056|1737804352195797032|[京东, 证据, 方法, 商家, ...|(642,[3,7,17,18,2...|\n",
      "|1737804352476815360|1737804352195797043|  [百科, HDR, 火热, 技术,...|(642,[0,3,5,14,15...|\n",
      "|1737804352485203968|1737804352195797004| [吴亦凡, 代言, 小米, MIX...|(642,[1,2,9,11,15...|\n",
      "|1737804352489398272|1737804352195797026|[谷歌, 图像, 分层, 算法, ...|(642,[5,11,15,37,...|\n",
      "|1737804352493592576|1737804352199991296|[汽车, 三网, 智能, 平台, ...|(642,[0,4,11,20,2...|\n",
      "|1737804352497786880|1737804352195797044|      [APP, APP, 总结, AP...|(642,[0,3,9,12,13...|\n",
      "|1737804352506175488|1737804352199991305|[亮屏, 小米, 机皇, 机上, ...|(642,[1,2,4,9,11,...|\n",
      "|1737804352514564096|1737804352195797039|  [雷军, 小米, MIX, 真机,...|(642,[1,2,9,11,15...|\n",
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cv_result.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# IDF 模型\n",
    "from pyspark.ml.feature import IDF\n",
    "idf = IDF(inputCol=\"countFeatures\", outputCol=\"idfFeatures\")\n",
    "idfModel = idf.fit(cv_result)\n",
    "idfModel.write().overwrite().save(\"hdfs://node1:8020/headlines/models/testIDF.model\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['用户',\n",
       " '小米',\n",
       " 'MIX',\n",
       " '产品',\n",
       " '智能',\n",
       " '技术',\n",
       " '卫玺',\n",
       " '商家',\n",
       " '马桶盖',\n",
       " '手机',\n",
       " '荣耀',\n",
       " '科技',\n",
       " '按钮',\n",
       " '验证码',\n",
       " 'HDR',\n",
       " '曝光',\n",
       " 'Dolby',\n",
       " '京东',\n",
       " '奶茶',\n",
       " '海尔',\n",
       " 'APP',\n",
       " '输入框',\n",
       " '车辆',\n",
       " '方面',\n",
       " '海报',\n",
       " '流程',\n",
       " '页面',\n",
       " '平台',\n",
       " '键盘',\n",
       " '语音',\n",
       " '功能',\n",
       " '密码',\n",
       " '体验',\n",
       " '数据',\n",
       " 'Vision',\n",
       " '方式',\n",
       " '视觉',\n",
       " '汽车',\n",
       " '图像',\n",
       " '网友',\n",
       " '人工智能',\n",
       " '全球',\n",
       " '证据',\n",
       " '负反馈',\n",
       " '中国',\n",
       " '笔者',\n",
       " '发布会',\n",
       " '时间',\n",
       " '时候',\n",
       " '新品',\n",
       " '生态',\n",
       " '消费者',\n",
       " '错误',\n",
       " '官方',\n",
       " '啤酒',\n",
       " '谷歌',\n",
       " '手机号',\n",
       " '新能源',\n",
       " '上海',\n",
       " '店里',\n",
       " '售假',\n",
       " '问题',\n",
       " '形式',\n",
       " '商品',\n",
       " '运维',\n",
       " 'HDR10',\n",
       " '猎云',\n",
       " '战略',\n",
       " '手机号码',\n",
       " '女士',\n",
       " '卫浴',\n",
       " '智慧',\n",
       " '阿迪达斯',\n",
       " '画面',\n",
       " '模式',\n",
       " '宣传语',\n",
       " '雷军',\n",
       " '场景',\n",
       " '标准',\n",
       " '次数',\n",
       " '家庭',\n",
       " '摄像头',\n",
       " '真机',\n",
       " '艺术',\n",
       " '广州',\n",
       " '市场',\n",
       " '吴亦凡',\n",
       " '方法',\n",
       " '后置',\n",
       " 'Contour',\n",
       " '世界',\n",
       " 'Comfort',\n",
       " '屏幕',\n",
       " '盈利',\n",
       " '算法',\n",
       " 'Facebook',\n",
       " '双手',\n",
       " 'com',\n",
       " '信息',\n",
       " '细节',\n",
       " '车型',\n",
       " '范围',\n",
       " '用户注册',\n",
       " 'tinder',\n",
       " '硬件',\n",
       " 'HDMI',\n",
       " '杉杉',\n",
       " '消息',\n",
       " '客服',\n",
       " '账户',\n",
       " '企业',\n",
       " '高达',\n",
       " 'bit',\n",
       " '华为',\n",
       " '张军伟',\n",
       " '淘宝',\n",
       " '数字',\n",
       " '指纹识别',\n",
       " '文章',\n",
       " '条件',\n",
       " '关键',\n",
       " '状态',\n",
       " '英文',\n",
       " '东西',\n",
       " '总结',\n",
       " '过程',\n",
       " '外观',\n",
       " '内容',\n",
       " '金额',\n",
       " '电视',\n",
       " '复选框',\n",
       " '单车',\n",
       " 'Note',\n",
       " '马桶',\n",
       " '色彩',\n",
       " '动态',\n",
       " '品牌',\n",
       " '记忆',\n",
       " '折旧费',\n",
       " '思路',\n",
       " '代言',\n",
       " '规模',\n",
       " '信号源',\n",
       " '光纤',\n",
       " '中文',\n",
       " '信心',\n",
       " '转化率',\n",
       " '单月',\n",
       " '团队',\n",
       " '邀请函',\n",
       " '北京',\n",
       " '资源',\n",
       " 'XXX',\n",
       " '视频',\n",
       " 'ilieyun',\n",
       " '代码',\n",
       " '讯号',\n",
       " '蓬皮杜',\n",
       " '配色',\n",
       " '前置',\n",
       " '植入',\n",
       " '软件',\n",
       " '卖家',\n",
       " '视觉效果',\n",
       " '个性化',\n",
       " '苹果',\n",
       " '无法',\n",
       " '协议',\n",
       " '关键性',\n",
       " '步骤',\n",
       " '全面',\n",
       " '北京大学',\n",
       " '奇瑞',\n",
       " '旗下',\n",
       " '潮流',\n",
       " '精彩',\n",
       " '云杉',\n",
       " '区分',\n",
       " '新闻',\n",
       " '深度',\n",
       " '比亚迪',\n",
       " '效果',\n",
       " '线下',\n",
       " '首款',\n",
       " 'FIBBR',\n",
       " '线缆',\n",
       " '心理',\n",
       " '滴滴',\n",
       " '解决方案',\n",
       " '新机',\n",
       " 'toast',\n",
       " '目标',\n",
       " 'http',\n",
       " '三网',\n",
       " '意义',\n",
       " 'www',\n",
       " '投影',\n",
       " '代表',\n",
       " '系统',\n",
       " '人员',\n",
       " 'LDR',\n",
       " 'Dynamic',\n",
       " '界面',\n",
       " '荣威',\n",
       " '互联网',\n",
       " '奶奶',\n",
       " 'A轮',\n",
       " '深圳',\n",
       " '有限公司',\n",
       " '原创',\n",
       " '九宫格',\n",
       " '锌合金',\n",
       " '色域',\n",
       " '分层',\n",
       " '利用',\n",
       " '手动',\n",
       " 'Play',\n",
       " '厂商',\n",
       " '天使',\n",
       " '关系',\n",
       " '公司',\n",
       " '方案',\n",
       " '人们',\n",
       " 'icon',\n",
       " '长距离',\n",
       " '成本',\n",
       " 'Google',\n",
       " '现场',\n",
       " '亮屏',\n",
       " '时尚',\n",
       " '业务',\n",
       " '优势',\n",
       " '充值',\n",
       " 'Range',\n",
       " '厕所',\n",
       " '电池',\n",
       " '亮度',\n",
       " '智行',\n",
       " '停车位',\n",
       " '能力',\n",
       " '前提',\n",
       " '节约',\n",
       " '评论',\n",
       " '核心技术',\n",
       " '国宾',\n",
       " '租赁承包',\n",
       " '挡箭牌',\n",
       " '标签',\n",
       " '梅耶斯',\n",
       " '门票',\n",
       " 'Toast',\n",
       " '基础设施',\n",
       " '管家',\n",
       " '专利费',\n",
       " '百科',\n",
       " '视界',\n",
       " '影音',\n",
       " '晚会',\n",
       " '机器',\n",
       " '投资人',\n",
       " '风潮',\n",
       " '大神',\n",
       " '帐户',\n",
       " '学士',\n",
       " '定局',\n",
       " '远高于',\n",
       " '核准',\n",
       " '温度',\n",
       " '原文',\n",
       " '水军',\n",
       " '焦点',\n",
       " '电话',\n",
       " '学府',\n",
       " '战线',\n",
       " '朋友',\n",
       " '单品',\n",
       " '残值',\n",
       " '单位',\n",
       " '第一版',\n",
       " '水压',\n",
       " '帐号',\n",
       " '基站',\n",
       " '项目',\n",
       " '联网',\n",
       " 'Twitter',\n",
       " '集团',\n",
       " '智能化',\n",
       " '凯利',\n",
       " '副总经理',\n",
       " '背景色',\n",
       " '家庭影院',\n",
       " '人群',\n",
       " 'EMERSON',\n",
       " '额头',\n",
       " '中断',\n",
       " '布线',\n",
       " '结论',\n",
       " '篇幅',\n",
       " '希尔顿酒店',\n",
       " '身份',\n",
       " '菲伯尔',\n",
       " '主机厂',\n",
       " '价值',\n",
       " '创始人',\n",
       " '普通',\n",
       " '资讯',\n",
       " '今天下午',\n",
       " '计划',\n",
       " '直观',\n",
       " '亮相',\n",
       " '群众',\n",
       " '中心',\n",
       " 'MBA',\n",
       " '胖子',\n",
       " '投影设备',\n",
       " '身体',\n",
       " '代言人',\n",
       " '里程',\n",
       " '共同点',\n",
       " '比比',\n",
       " '机器人',\n",
       " '图片',\n",
       " '武汉大学',\n",
       " 'Pre',\n",
       " '电动',\n",
       " '照片',\n",
       " '编辑',\n",
       " '社会',\n",
       " '家居',\n",
       " '运存',\n",
       " '题图',\n",
       " '生态圈',\n",
       " '程序员',\n",
       " '量子',\n",
       " '补救措施',\n",
       " '体重',\n",
       " '月份',\n",
       " '行业',\n",
       " 'Samsung',\n",
       " '模块',\n",
       " '格局',\n",
       " '感觉',\n",
       " '峰会',\n",
       " '阿迪',\n",
       " '马云',\n",
       " '图形',\n",
       " 'Test',\n",
       " '开发者',\n",
       " '人人',\n",
       " '领域',\n",
       " '出租率',\n",
       " '下单',\n",
       " '用家',\n",
       " '插线',\n",
       " '红米',\n",
       " '托斯卡纳',\n",
       " '处理器',\n",
       " '体会',\n",
       " '文本',\n",
       " '性能',\n",
       " '石门',\n",
       " '年龄',\n",
       " '保证车辆',\n",
       " '层面',\n",
       " '声纹识别',\n",
       " '正确性',\n",
       " '好友',\n",
       " '有点',\n",
       " '科学',\n",
       " '错货',\n",
       " 'AWE2018',\n",
       " '鹏城',\n",
       " '国产',\n",
       " '控制权',\n",
       " '收费',\n",
       " '牌照',\n",
       " '经验',\n",
       " '佼佼者',\n",
       " '正确引导',\n",
       " '银行账号',\n",
       " '表格',\n",
       " '中国科大',\n",
       " 'DeepLab',\n",
       " '火热',\n",
       " '国际',\n",
       " '网站',\n",
       " '因素',\n",
       " '脑洞',\n",
       " '博物馆',\n",
       " '手感',\n",
       " '条款',\n",
       " '语言',\n",
       " '程度',\n",
       " '第三版',\n",
       " '可视化',\n",
       " '插座',\n",
       " '腾讯',\n",
       " 'SONY',\n",
       " '编织',\n",
       " '销量',\n",
       " '记录',\n",
       " '真实性',\n",
       " '割舍',\n",
       " '关键在于',\n",
       " '一键',\n",
       " '大厂',\n",
       " 'EU260',\n",
       " '佳丽',\n",
       " '建议',\n",
       " '关键步骤',\n",
       " '观众',\n",
       " '布局',\n",
       " '国人',\n",
       " '新奇',\n",
       " '护腰',\n",
       " '孩子',\n",
       " '可能性',\n",
       " '主打',\n",
       " '概率',\n",
       " '供应链',\n",
       " '研讨会',\n",
       " '国家',\n",
       " '试衣间',\n",
       " '年度',\n",
       " '格式',\n",
       " '大事儿',\n",
       " '产业',\n",
       " 'input',\n",
       " '边框',\n",
       " '吸引力',\n",
       " '卓越',\n",
       " '影响力',\n",
       " '福源',\n",
       " '源代码',\n",
       " 'airbnb',\n",
       " '壁垒',\n",
       " '邮箱',\n",
       " '定格',\n",
       " '部分',\n",
       " '普适性',\n",
       " '全网',\n",
       " '水温',\n",
       " '广泛性',\n",
       " '弱化',\n",
       " '后台',\n",
       " '机上',\n",
       " '计算机',\n",
       " '营收',\n",
       " '话题',\n",
       " '毕业',\n",
       " '橙色',\n",
       " '徐征鹏',\n",
       " '力求',\n",
       " '过分',\n",
       " '小时',\n",
       " '核心',\n",
       " '主宰者',\n",
       " '兴趣',\n",
       " '对话框',\n",
       " '衣服',\n",
       " '言论',\n",
       " '物体',\n",
       " '无线',\n",
       " '侧重点',\n",
       " '高分辨率',\n",
       " '个性',\n",
       " '大学',\n",
       " '巨头',\n",
       " '租金',\n",
       " '运营商',\n",
       " '成员',\n",
       " 'lieyunwang',\n",
       " 'yue',\n",
       " '过洋',\n",
       " '大中华',\n",
       " 'disabled',\n",
       " 'E550',\n",
       " '活性',\n",
       " '经理',\n",
       " '镜头',\n",
       " '绿色',\n",
       " '等车',\n",
       " '参数',\n",
       " '一体',\n",
       " '老人',\n",
       " '购物',\n",
       " '法宝',\n",
       " '姿态',\n",
       " '消失',\n",
       " '受众',\n",
       " '个人',\n",
       " '芯片',\n",
       " '轻量化',\n",
       " '高效能',\n",
       " '灰色',\n",
       " '形象',\n",
       " '日本',\n",
       " 'archives',\n",
       " '增幅',\n",
       " '时长',\n",
       " 'joy',\n",
       " '科学技术',\n",
       " 'ERX5',\n",
       " '服务商',\n",
       " '规则',\n",
       " '资料',\n",
       " '表单',\n",
       " '总监',\n",
       " '视线',\n",
       " '储能',\n",
       " '利益',\n",
       " '旗舰',\n",
       " 'Pixel',\n",
       " '星球',\n",
       " '习惯',\n",
       " '看点',\n",
       " 'Dialog',\n",
       " '事故',\n",
       " '纪念版',\n",
       " '无感',\n",
       " '效率',\n",
       " '无人驾驶',\n",
       " '董事长',\n",
       " '片源',\n",
       " '生物',\n",
       " '排风扇',\n",
       " '负荷',\n",
       " '气球',\n",
       " '专利',\n",
       " '幻想',\n",
       " '专营店',\n",
       " '普天',\n",
       " 'Low',\n",
       " '城市',\n",
       " '意图',\n",
       " '景深',\n",
       " '弗朗西',\n",
       " '地方',\n",
       " '头条',\n",
       " '桌面',\n",
       " '商业',\n",
       " '科技前沿',\n",
       " '两全',\n",
       " 'High',\n",
       " '现实',\n",
       " '用品',\n",
       " '魔法',\n",
       " '资源优势',\n",
       " '下巴',\n",
       " '专业',\n",
       " '冲击力',\n",
       " '长度',\n",
       " '编程语言',\n",
       " '凯文',\n",
       " '阿里巴巴',\n",
       " '大盗',\n",
       " '先人',\n",
       " '明文',\n",
       " '窗户',\n",
       " '信号',\n",
       " '热点',\n",
       " '高空',\n",
       " '不太想',\n",
       " '黑锅',\n",
       " 'CC0',\n",
       " '任务',\n",
       " '咒语',\n",
       " 'PET',\n",
       " '代价',\n",
       " '表现力',\n",
       " 'MIX2S',\n",
       " '动作',\n",
       " '政策',\n",
       " '新旗舰',\n",
       " '基础',\n",
       " '颜值',\n",
       " '保持一致',\n",
       " '正面图',\n",
       " '洗车',\n",
       " '双方',\n",
       " '前提条件',\n",
       " '上线',\n",
       " '原则',\n",
       " 'PEXELS',\n",
       " '网络',\n",
       " '杜比',\n",
       " 'pao',\n",
       " '美团',\n",
       " '数字化',\n",
       " '淘宝网',\n",
       " '应卫敏',\n",
       " '榜样',\n",
       " '一流',\n",
       " '如厕',\n",
       " '潮牌',\n",
       " '同事',\n",
       " '餐单',\n",
       " '摩拜',\n",
       " '关联',\n",
       " '资金',\n",
       " '短信',\n",
       " '广告语',\n",
       " '锂电',\n",
       " '南开大学',\n",
       " '链接',\n",
       " '名称',\n",
       " '管理局',\n",
       " '图像处理',\n",
       " '国家标准',\n",
       " '聊天',\n",
       " '维权',\n",
       " '环节',\n",
       " '环境',\n",
       " 'Gbps',\n",
       " '画质',\n",
       " '邮电大学',\n",
       " '特殊人群',\n",
       " '机皇',\n",
       " '产生',\n",
       " '电子',\n",
       " '长租',\n",
       " '方向',\n",
       " '趋势',\n",
       " '手照',\n",
       " '电动车',\n",
       " '峰值',\n",
       " '专用',\n",
       " '战略目标',\n",
       " '智造',\n",
       " '纠正错误',\n",
       " '热水器',\n",
       " '艳阳',\n",
       " '家用',\n",
       " '清空',\n",
       " '空间',\n",
       " '网址',\n",
       " '原因',\n",
       " '颜色',\n",
       " '座圈',\n",
       " '歌单',\n",
       " '孕妇',\n",
       " '大家']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以进行转换\n",
    "cv_m.vocabulary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.6061358 , 1.01160091, 1.01160091, 0.6061358 , 1.01160091,\n",
       "       1.01160091, 1.70474809, 1.70474809, 1.70474809, 0.6061358 ,\n",
       "       1.70474809, 0.31845373, 1.70474809, 1.70474809, 1.70474809,\n",
       "       0.45198512, 1.70474809, 1.29928298, 1.70474809, 1.70474809])"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idfModel.idf.toArray()[:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IDF对CV结果进行计算TFIDF\n",
    "from pyspark.ml.feature import IDFModel\n",
    "idf_model = IDFModel.load(\"hdfs://node1:8020/headlines/models/testIDF.model\")\n",
    "tfidf_res = idf_model.transform(cv_result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "keywords_list_with_idf = list(zip(cv_m.vocabulary, idf_model.idf.toArray()))\n",
    "def func(data):\n",
    "   for index in range(len(data)):\n",
    "       data[index] = list(data[index])\n",
    "       data[index].append(index)\n",
    "       data[index][1] = float(data[index][1])\n",
    "func(keywords_list_with_idf)\n",
    "sc = oa.spark.sparkContext\n",
    "rdd = sc.parallelize(keywords_list_with_idf)\n",
    "df = rdd.toDF([\"keywords\", \"idf\", \"index\"])\n",
    "\n",
    "df.write.insertInto('idf_keywords_values')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.836 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------------+--------------------+--------------------+\n",
      "|         article_id|         channel_id|                       words|       countFeatures|         idfFeatures|\n",
      "+-------------------+-------------------+----------------------------+--------------------+--------------------+\n",
      "|1737804352460038144|1737804352195797007|[荣耀, 阿迪, 功能, 手机, ...|(642,[0,3,9,10,11...|(642,[0,3,9,10,11...|\n",
      "|1737804352468426752|1737804352195797004|[语音, 双手, 海尔, 卫玺, ...|(642,[0,3,4,5,6,8...|(642,[0,3,4,5,6,8...|\n",
      "|1737804352472621056|1737804352195797032|[京东, 证据, 方法, 商家, ...|(642,[3,7,17,18,2...|(642,[3,7,17,18,2...|\n",
      "|1737804352476815360|1737804352195797043|  [百科, HDR, 火热, 技术,...|(642,[0,3,5,14,15...|(642,[0,3,5,14,15...|\n",
      "|1737804352485203968|1737804352195797004| [吴亦凡, 代言, 小米, MIX...|(642,[1,2,9,11,15...|(642,[1,2,9,11,15...|\n",
      "|1737804352489398272|1737804352195797026|[谷歌, 图像, 分层, 算法, ...|(642,[5,11,15,37,...|(642,[5,11,15,37,...|\n",
      "|1737804352493592576|1737804352199991296|[汽车, 三网, 智能, 平台, ...|(642,[0,4,11,20,2...|(642,[0,4,11,20,2...|\n",
      "|1737804352497786880|1737804352195797044|      [APP, APP, 总结, AP...|(642,[0,3,9,12,13...|(642,[0,3,9,12,13...|\n",
      "|1737804352506175488|1737804352199991305|[亮屏, 小米, 机皇, 机上, ...|(642,[1,2,4,9,11,...|(642,[1,2,4,9,11,...|\n",
      "|1737804352514564096|1737804352195797039|  [雷军, 小米, MIX, 真机,...|(642,[1,2,9,11,15...|(642,[1,2,9,11,15...|\n",
      "+-------------------+-------------------+----------------------------+--------------------+--------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "tfidf_res.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 1265词的 {索引 以及 权重}\n",
    "def func(partition):\n",
    "    TOPK = 20\n",
    "    for row in partition:\n",
    "        # 找到索引与IDF值并进行排序\n",
    "        _ = list(zip(row.idfFeatures.indices, row.idfFeatures.values))\n",
    "        _ = sorted(_, key=lambda x: x[1], reverse=True)\n",
    "        result = _[:TOPK]\n",
    "        for word_index, tfidf in result:\n",
    "            yield row.article_id, row.channel_id, int(word_index), round(float(tfidf), 4)\n",
    "kewords_tfidf = tfidf_res.rdd.mapPartitions(func).toDF(['article_id', 'channel_id', 'index', 'weights'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 44:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-----+-------+\n",
      "|         article_id|         channel_id|index|weights|\n",
      "+-------------------+-------------------+-----+-------+\n",
      "|1737804352460038144|1737804352195797007|   10|23.8665|\n",
      "|1737804352460038144|1737804352195797007|   72|  6.819|\n",
      "|1737804352460038144|1737804352195797007|   98| 5.1142|\n",
      "|1737804352460038144|1737804352195797007|    9| 3.6368|\n",
      "|1737804352460038144|1737804352195797007|  158| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|  174| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|  205| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|  229| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|  107| 2.5986|\n",
      "|1737804352460038144|1737804352195797007|  117| 2.5986|\n",
      "|1737804352460038144|1737804352195797007|  136| 2.5986|\n",
      "|1737804352460038144|1737804352195797007|   47| 2.0232|\n",
      "|1737804352460038144|1737804352195797007|   30| 1.8184|\n",
      "|1737804352460038144|1737804352195797007|  260| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  266| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  313| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  315| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  335| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  339| 1.7047|\n",
      "|1737804352460038144|1737804352195797007|  343| 1.7047|\n",
      "+-------------------+-------------------+-----+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "kewords_tfidf.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 利用keywordsIndex = ktt.spark.sql(\"select keyword, index idx from idf_keywords_values\")中标，知道索引对应的词\n",
    "idf_keywords_values = oa.spark.sql(\"select keyword, index idx from idf_keywords_values\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+--------+-------+\n",
      "|         article_id|         channel_id| keyword|weights|\n",
      "+-------------------+-------------------+--------+-------+\n",
      "|1737804352460038144|1737804352195797007|    荣耀|23.8665|\n",
      "|1737804352460038144|1737804352195797007|    荣耀|23.8665|\n",
      "|1737804352460038144|1737804352195797007|    荣耀|23.8665|\n",
      "|1737804352460038144|1737804352195797007|    荣耀|23.8665|\n",
      "|1737804352460038144|1737804352195797007|阿迪达斯|  6.819|\n",
      "|1737804352460038144|1737804352195797007|阿迪达斯|  6.819|\n",
      "|1737804352460038144|1737804352195797007|阿迪达斯|  6.819|\n",
      "|1737804352460038144|1737804352195797007|阿迪达斯|  6.819|\n",
      "|1737804352460038144|1737804352195797007|    信息| 5.1142|\n",
      "|1737804352460038144|1737804352195797007|    信息| 5.1142|\n",
      "|1737804352460038144|1737804352195797007|    信息| 5.1142|\n",
      "|1737804352460038144|1737804352195797007|    信息| 5.1142|\n",
      "|1737804352460038144|1737804352195797007|    手机| 3.6368|\n",
      "|1737804352460038144|1737804352195797007|    手机| 3.6368|\n",
      "|1737804352460038144|1737804352195797007|    手机| 3.6368|\n",
      "|1737804352460038144|1737804352195797007|    手机| 3.6368|\n",
      "|1737804352460038144|1737804352195797007|    配色| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|    配色| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|    配色| 3.4095|\n",
      "|1737804352460038144|1737804352195797007|    配色| 3.4095|\n",
      "+-------------------+-------------------+--------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "keyword_str_tfidf = kewords_tfidf.join(idf_keywords_values, idf_keywords_values.idx==kewords_tfidf.index).select([\"article_id\", \"channel_id\", \"keyword\", \"weights\"])\n",
    "\n",
    "keyword_str_tfidf.show()\n",
    "keyword_str_tfidf.write.insertInto(\"tfidf_keywords_values\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# texrank\n",
    "# 分词\n",
    "def textrank(partition):\n",
    "    import os\n",
    "\n",
    "    import jieba\n",
    "    import jieba.analyse\n",
    "    import jieba.posseg as pseg\n",
    "    import codecs\n",
    "\n",
    "    abspath = \"/export/server/toutiao/words\"\n",
    "\n",
    "    # 结巴加载用户词典\n",
    "    userDict_path = os.path.join(abspath, \"ITKeywords.txt\")\n",
    "    jieba.load_userdict(userDict_path)\n",
    "\n",
    "    # 停用词文本\n",
    "    stopwords_path = os.path.join(abspath, \"stop_words.txt\")\n",
    "\n",
    "    def get_stopwords_list():\n",
    "        \"\"\"返回stopwords列表\"\"\"\n",
    "        stopwords_list = [i.strip()\n",
    "                          for i in codecs.open(stopwords_path).readlines()]\n",
    "        return stopwords_list\n",
    "\n",
    "    # 所有的停用词列表\n",
    "    stopwords_list = get_stopwords_list()\n",
    "\n",
    "    class TextRank(jieba.analyse.TextRank):\n",
    "        def __init__(self, window=20, word_min_len=2):\n",
    "            super(TextRank, self).__init__()\n",
    "            self.span = window  # 窗口大小\n",
    "            self.word_min_len = word_min_len  # 单词的最小长度\n",
    "            # 要保留的词性，根据jieba github ，具体参见https://github.com/baidu/lac\n",
    "            self.pos_filt = frozenset(\n",
    "                ('n', 'x', 'eng', 'f', 's', 't', 'nr', 'ns', 'nt', \"nw\", \"nz\", \"PER\", \"LOC\", \"ORG\"))\n",
    "\n",
    "        def pairfilter(self, wp):\n",
    "            \"\"\"过滤条件，返回True或者False\"\"\"\n",
    "\n",
    "            if wp.flag == \"eng\":\n",
    "                if len(wp.word) <= 2:\n",
    "                    return False\n",
    "\n",
    "            if wp.flag in self.pos_filt and len(wp.word.strip()) >= self.word_min_len \\\n",
    "                    and wp.word.lower() not in stopwords_list:\n",
    "                return True\n",
    "    # TextRank过滤窗口大小为5，单词最小为2\n",
    "    textrank_model = TextRank(window=5, word_min_len=2)\n",
    "    allowPOS = ('n', \"x\", 'eng', 'nr', 'ns', 'nt', \"nw\", \"nz\", \"c\")\n",
    "\n",
    "    for row in partition:\n",
    "        tags = textrank_model.textrank(row.sentence, topK=20, withWeight=True, allowPOS=allowPOS, withFlag=False)\n",
    "        for tag in tags:\n",
    "            yield row.article_id, row.channel_id, tag[0], tag[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "textrank = article_data.rdd.mapPartitions(textrank).toDF([\"article_id\", \"channel_id\", \"keyword\", \"textrank\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.954 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+--------+-------------------+\n",
      "|         article_id|         channel_id| keyword|           textrank|\n",
      "+-------------------+-------------------+--------+-------------------+\n",
      "|1737804352460038144|1737804352195797007|    荣耀|                1.0|\n",
      "|1737804352460038144|1737804352195797007|    手机|  0.516953481537396|\n",
      "|1737804352460038144|1737804352195797007|     img|0.37434172502638496|\n",
      "|1737804352460038144|1737804352195797007|    功能|0.35269171953409895|\n",
      "|1737804352460038144|1737804352195797007|   large| 0.2781438625919395|\n",
      "|1737804352460038144|1737804352195797007|   image|0.26991109558213894|\n",
      "|1737804352460038144|1737804352195797007|    奶奶|0.26148862492656333|\n",
      "|1737804352460038144|1737804352195797007|    曝光| 0.2445434131620249|\n",
      "|1737804352460038144|1737804352195797007|    时间| 0.2209522530463879|\n",
      "|1737804352460038144|1737804352195797007|    消息|0.21697467717430569|\n",
      "|1737804352460038144|1737804352195797007|     pgc|0.21642177217824218|\n",
      "|1737804352460038144|1737804352195797007|     alt| 0.2035291862906526|\n",
      "|1737804352460038144|1737804352195797007|    而言|0.20155886506669174|\n",
      "|1737804352460038144|1737804352195797007|    品牌| 0.2013877762466845|\n",
      "|1737804352460038144|1737804352195797007|     com| 0.2009969909987098|\n",
      "|1737804352460038144|1737804352195797007|    信息|0.19637939035278493|\n",
      "|1737804352460038144|1737804352195797007|    方面|  0.191023820846971|\n",
      "|1737804352460038144|1737804352195797007|    潮流|0.18883917986094886|\n",
      "|1737804352460038144|1737804352195797007|指纹识别|0.18808329302400617|\n",
      "|1737804352460038144|1737804352195797007|    时尚|0.18702767931139408|\n",
      "+-------------------+-------------------+--------+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "textrank.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 文章画像 关键词与权重合并\n",
    "# textrank * idf\n",
    "idf_keywords_values = oa.spark.sql(\"select * from idf_keywords_values\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----+\n",
      "|keyword|               idf|index|\n",
      "+-------+------------------+-----+\n",
      "|   用户|0.6061358035703155|    0|\n",
      "|   小米|1.0116009116784799|    1|\n",
      "|    MIX|1.0116009116784799|    2|\n",
      "|   产品|0.6061358035703155|    3|\n",
      "|   智能|1.0116009116784799|    4|\n",
      "|   技术|1.0116009116784799|    5|\n",
      "|   卫玺|1.7047480922384253|    6|\n",
      "|   商家|1.7047480922384253|    7|\n",
      "| 马桶盖|1.7047480922384253|    8|\n",
      "|   手机|0.6061358035703155|    9|\n",
      "|   荣耀|1.7047480922384253|   10|\n",
      "|   科技|0.3184537311185346|   11|\n",
      "|   按钮|1.7047480922384253|   12|\n",
      "| 验证码|1.7047480922384253|   13|\n",
      "|    HDR|1.7047480922384253|   14|\n",
      "|   曝光|0.4519851237430572|   15|\n",
      "|  Dolby|1.7047480922384253|   16|\n",
      "|   京东|1.2992829841302609|   17|\n",
      "|   奶茶|1.7047480922384253|   18|\n",
      "|   海尔|1.7047480922384253|   19|\n",
      "+-------+------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idf_keywords_values.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "keywords_res = textrank.join(idf_keywords_values, on=['keyword'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.907 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+-------------------+-------------------+------------------+-----+\n",
      "|keyword|         article_id|         channel_id|           textrank|               idf|index|\n",
      "+-------+-------------------+-------------------+-------------------+------------------+-----+\n",
      "|   荣耀|1737804352460038144|1737804352195797007|                1.0|1.7047480922384253|   10|\n",
      "|   荣耀|1737804352460038144|1737804352195797007|                1.0|1.7047480922384253|   10|\n",
      "|   荣耀|1737804352460038144|1737804352195797007|                1.0|1.7047480922384253|   10|\n",
      "|   荣耀|1737804352460038144|1737804352195797007|                1.0|1.7047480922384253|   10|\n",
      "|   手机|1737804352460038144|1737804352195797007|  0.516953481537396|0.6061358035703155|    9|\n",
      "|   手机|1737804352460038144|1737804352195797007|  0.516953481537396|0.6061358035703155|    9|\n",
      "|   手机|1737804352460038144|1737804352195797007|  0.516953481537396|0.6061358035703155|    9|\n",
      "|   手机|1737804352460038144|1737804352195797007|  0.516953481537396|0.6061358035703155|    9|\n",
      "|    img|1737804352460038144|1737804352195797007|0.37434172502638496|              null| null|\n",
      "|   功能|1737804352460038144|1737804352195797007|0.35269171953409895|0.6061358035703155|   30|\n",
      "|   功能|1737804352460038144|1737804352195797007|0.35269171953409895|0.6061358035703155|   30|\n",
      "|   功能|1737804352460038144|1737804352195797007|0.35269171953409895|0.6061358035703155|   30|\n",
      "|   功能|1737804352460038144|1737804352195797007|0.35269171953409895|0.6061358035703155|   30|\n",
      "|  large|1737804352460038144|1737804352195797007| 0.2781438625919395|              null| null|\n",
      "|  image|1737804352460038144|1737804352195797007|0.26991109558213894|              null| null|\n",
      "|   奶奶|1737804352460038144|1737804352195797007|0.26148862492656333|1.7047480922384253|  205|\n",
      "|   奶奶|1737804352460038144|1737804352195797007|0.26148862492656333|1.7047480922384253|  205|\n",
      "|   奶奶|1737804352460038144|1737804352195797007|0.26148862492656333|1.7047480922384253|  205|\n",
      "|   奶奶|1737804352460038144|1737804352195797007|0.26148862492656333|1.7047480922384253|  205|\n",
      "|   曝光|1737804352460038144|1737804352195797007| 0.2445434131620249|0.4519851237430572|   15|\n",
      "+-------+-------------------+-------------------+-------------------+------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "keywords_res.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "keywords_weights = keywords_res.withColumn('weights', keywords_res.textrank * keywords_res.idf).select([\"article_id\", \"channel_id\", \"keyword\", \"weights\"])\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.811 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+-------+------------------+\n",
      "|         article_id|         channel_id|keyword|           weights|\n",
      "+-------------------+-------------------+-------+------------------+\n",
      "|1737804352460038144|1737804352195797007|   荣耀|1.7047480922384253|\n",
      "|1737804352460038144|1737804352195797007|   荣耀|1.7047480922384253|\n",
      "|1737804352460038144|1737804352195797007|   荣耀|1.7047480922384253|\n",
      "|1737804352460038144|1737804352195797007|   荣耀|1.7047480922384253|\n",
      "|1737804352460038144|1737804352195797007|   手机|0.3133440139401418|\n",
      "|1737804352460038144|1737804352195797007|   手机|0.3133440139401418|\n",
      "|1737804352460038144|1737804352195797007|   手机|0.3133440139401418|\n",
      "|1737804352460038144|1737804352195797007|   手机|0.3133440139401418|\n",
      "|1737804352460038144|1737804352195797007|    img|              null|\n",
      "|1737804352460038144|1737804352195797007|   功能|0.2137790788323974|\n",
      "|1737804352460038144|1737804352195797007|   功能|0.2137790788323974|\n",
      "|1737804352460038144|1737804352195797007|   功能|0.2137790788323974|\n",
      "|1737804352460038144|1737804352195797007|   功能|0.2137790788323974|\n",
      "|1737804352460038144|1737804352195797007|  large|              null|\n",
      "|1737804352460038144|1737804352195797007|  image|              null|\n",
      "|1737804352460038144|1737804352195797007|   奶奶| 0.445772234485608|\n",
      "|1737804352460038144|1737804352195797007|   奶奶| 0.445772234485608|\n",
      "|1737804352460038144|1737804352195797007|   奶奶| 0.445772234485608|\n",
      "|1737804352460038144|1737804352195797007|   奶奶| 0.445772234485608|\n",
      "|1737804352460038144|1737804352195797007|   曝光|0.1105299848585874|\n",
      "+-------------------+-------------------+-------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "keywords_weights.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/root/anaconda3/envs/toutiao/lib/python3.9/site-packages/pyspark/sql/dataframe.py:330: FutureWarning: Deprecated in 2.0, use createOrReplaceTempView instead.\n",
      "  warnings.warn(\"Deprecated in 2.0, use createOrReplaceTempView instead.\", FutureWarning)\n"
     ]
    }
   ],
   "source": [
    "keywords_weights.registerTempTable('temp')\n",
    "\n",
    "keywords_weights = oa.spark.sql(\"select article_id, min(channel_id) channel_id, collect_list(keyword) keywords, collect_list(weights) weights from temp group by article_id\")\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...                (0 + 1) / 1]\n",
      "Loading model from cache /tmp/jieba.cache\n",
      "Loading model cost 0.881 seconds.\n",
      "Prefix dict has been built successfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "|         article_id|         channel_id|                    keywords|             weights|\n",
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "|1737804352476815360|1737804352195797043|[技术, 技术, 技术, 技术, ...|[1.01160091167847...|\n",
      "|1737804352493592576|1737804352199991296|[车辆, 车辆, 车辆, 车辆, ...|[1.70474809223842...|\n",
      "|1737804352506175488|1737804352199991305|[小米, 小米, 小米, 小米, ...|[1.01160091167847...|\n",
      "|1737804352489398272|1737804352195797026|    [strong, 谷歌, 谷歌, ...|[1.65809819704557...|\n",
      "|1737804352460038144|1737804352195797007|[荣耀, 荣耀, 荣耀, 荣耀, ...|[1.70474809223842...|\n",
      "|1737804352468426752|1737804352195797004|[智能, 智能, 智能, 智能, ...|[1.01160091167847...|\n",
      "|1737804352472621056|1737804352195797032|[商家, 商家, 商家, 商家, ...|[1.70474809223842...|\n",
      "|1737804352485203968|1737804352195797004|[小米, 小米, 小米, 小米, ...|[1.01160091167847...|\n",
      "|1737804352514564096|1737804352195797039|[小米, 小米, 小米, 小米, ...|[1.01160091167847...|\n",
      "|1737804352497786880|1737804352195797044|[用户, 用户, 用户, 用户, ...|[0.60613580357031...|\n",
      "+-------------------+-------------------+----------------------------+--------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "keywords_weights.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 合并关键词和权重到字典\n",
    "def _func(row):\n",
    "    return row.article_id, row.channel_id, dict(zip(row.keywords, row.weights))\n",
    "\n",
    "article_kewords = keywords_weights.rdd.map(_func).toDF(['article_id', 'channel_id', 'keywords'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------+\n",
      "|         article_id|         channel_id|              keywords|\n",
      "+-------------------+-------------------+----------------------+\n",
      "|1737804352476815360|1737804352195797043|  {com -> 0.4907094...|\n",
      "|1737804352493592576|1737804352199991296|  {com -> 0.6790569...|\n",
      "|1737804352506175488|1737804352199991305|{下巴 -> 0.16728391...|\n",
      "|1737804352489398272|1737804352195797026|  {com -> 0.7300264...|\n",
      "|1737804352460038144|1737804352195797007|  {com -> 0.1157863...|\n",
      "|1737804352468426752|1737804352195797004|{科技 -> 0.39650368...|\n",
      "|1737804352472621056|1737804352195797032|  {img -> 0.5768701...|\n",
      "|1737804352485203968|1737804352195797004|  {com -> 0.1985088...|\n",
      "|1737804352514564096|1737804352195797039|  {com -> 0.3313308...|\n",
      "|1737804352497786880|1737804352195797044|  {com -> 0.2301249...|\n",
      "+-------------------+-------------------+----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "article_kewords.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算tfidf与texrank共同词作为主题词\n",
    "topic_sql = \"select t.article_id article_id2, collect_set(t.keyword) topics from tfidf_keywords_values t inner join textrank_keywords_values r where t.keyword=r.keyword group by article_id2\"\n",
    "article_topics = oa.spark.sql(topic_sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-----------------------------+\n",
      "|        article_id2|                       topics|\n",
      "+-------------------+-----------------------------+\n",
      "|1737804352476815360|       [HDR, 色彩, Vision,...|\n",
      "|1737804352493592576| [运维, 杉杉, 汽车, 广州, ...|\n",
      "|1737804352506175488|   [MIX, 机上, 官方, 小米,...|\n",
      "|1737804352489398272|     [Google, 代码, 谷歌, ...|\n",
      "|1737804352460038144| [时间, 消息, 潮流, 奶奶, ...|\n",
      "|1737804352468426752| [卫浴, 生态, 语音, 家庭, ...|\n",
      "|1737804352472621056| [淘宝, 卖家, 商品, 商家, ...|\n",
      "|1737804352485203968|  [代言, 吴亦凡, MIX, 新品...|\n",
      "|1737804352514564096|  [曝光, MIX, 摄像头, 新品...|\n",
      "|1737804352497786880|[键盘, 按钮, 密码, 手机号,...|\n",
      "+-------------------+-----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "article_topics.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------+-----------------------------+\n",
      "|         article_id|         channel_id|              keywords|                       topics|\n",
      "+-------------------+-------------------+----------------------+-----------------------------+\n",
      "|1737804352476815360|1737804352195797043|  {com -> 0.4907094...|       [HDR, 色彩, Vision,...|\n",
      "|1737804352493592576|1737804352199991296|  {com -> 0.6790569...| [运维, 杉杉, 汽车, 广州, ...|\n",
      "|1737804352506175488|1737804352199991305|{下巴 -> 0.16728391...|   [MIX, 机上, 官方, 小米,...|\n",
      "|1737804352489398272|1737804352195797026|  {com -> 0.7300264...|     [Google, 代码, 谷歌, ...|\n",
      "|1737804352460038144|1737804352195797007|  {com -> 0.1157863...| [时间, 消息, 潮流, 奶奶, ...|\n",
      "|1737804352468426752|1737804352195797004|{科技 -> 0.39650368...| [卫浴, 生态, 语音, 家庭, ...|\n",
      "|1737804352472621056|1737804352195797032|  {img -> 0.5768701...| [淘宝, 卖家, 商品, 商家, ...|\n",
      "|1737804352485203968|1737804352195797004|  {com -> 0.1985088...|  [代言, 吴亦凡, MIX, 新品...|\n",
      "|1737804352514564096|1737804352195797039|  {com -> 0.3313308...|  [曝光, MIX, 摄像头, 新品...|\n",
      "|1737804352497786880|1737804352195797044|  {com -> 0.2301249...|[键盘, 按钮, 密码, 手机号,...|\n",
      "+-------------------+-------------------+----------------------+-----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 关键词与主题词结果合并，得到文章的最终完整画像\n",
    "article_profile = article_kewords.join(article_topics, article_kewords.article_id==article_topics.article_id2).select([\"article_id\", \"channel_id\", \"keywords\", \"topics\"])\n",
    "article_profile.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 做词向量模型的训练\n",
    "# 通过少量数据来演示训练过程\n",
    "from pyspark.ml.feature import Word2Vec\n",
    "\n",
    "w2v =  Word2Vec(vectorSize=100, inputCol=\"words\", outputCol=\"model\", minCount=3)\n",
    "w2v_model =  w2v.fit(words_df)\n",
    "w2v_model.write().overwrite().save(\"hdfs://node1:8020/headlines/models/test.word2vec\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 求出增量文章的词向量，增量文章一共10篇文章\n",
    "# 1、加载某个频道模型，得到每个词的向量\n",
    "from pyspark.ml.feature import Word2VecModel\n",
    "\n",
    "word_vec = Word2VecModel.load(\"hdfs://node1:8020/headlines/models/test.word2vec\")\n",
    "vectors = word_vec.getVectors()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+--------------------+\n",
      "|  word|              vector|\n",
      "+------+--------------------+\n",
      "|  账户|[-0.0013602146646...|\n",
      "|  语音|[-0.0027449589688...|\n",
      "|  流程|[-0.0094633046537...|\n",
      "|  范围|[-0.0038101593963...|\n",
      "|  模式|[0.01048853248357...|\n",
      "|  单车|[1.72028783708810...|\n",
      "|  官方|[0.00468378001824...|\n",
      "|  企业|[-0.0028421240858...|\n",
      "|  时间|[-0.0014943524729...|\n",
      "|  售假|[-0.0017417649505...|\n",
      "|  产品|[-0.0083605144172...|\n",
      "|  键盘|[-0.0052899532020...|\n",
      "|  上海|[0.00205903733149...|\n",
      "|  卫玺|[0.00299180345609...|\n",
      "|  客服|[0.00232487753964...|\n",
      "|马桶盖|[0.00453051738440...|\n",
      "|  电视|[-0.0032608564943...|\n",
      "|手机号|[0.00188732240349...|\n",
      "|  车型|[-0.0033336679916...|\n",
      "|   bit|[0.00105186353903...|\n",
      "+------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "vectors.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2、获取频道的文章画像，得到文章画像的关键词，找到这些文章关键词对应的词向量，这里举例子python\n",
    "python_article_profile = article_profile.filter('channel_id = 1737804352195797004')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+----------------------+----------------------------+\n",
      "|         article_id|         channel_id|              keywords|                      topics|\n",
      "+-------------------+-------------------+----------------------+----------------------------+\n",
      "|1737804352468426752|1737804352195797004|{科技 -> 0.39650368...|[卫浴, 生态, 语音, 家庭, ...|\n",
      "|1737804352485203968|1737804352195797004|  {com -> 0.1985088...| [代言, 吴亦凡, MIX, 新品...|\n",
      "+-------------------+-------------------+----------------------+----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "python_article_profile.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "python_article_profile.registerTempTable(\"profile\")\n",
    "articleKeywordsWeights = oa.spark.sql(\n",
    "                \"select article_id, channel_id, keyword, weight from profile LATERAL VIEW explode(keywords) AS keyword, weight\")\n",
    "article_keyword_vec_weights = articleKeywordsWeights.join(vectors, vectors.word==articleKeywordsWeights.keyword, \"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+--------+-------------------+--------+--------------------+\n",
      "|         article_id|         channel_id| keyword|             weight|    word|              vector|\n",
      "+-------------------+-------------------+--------+-------------------+--------+--------------------+\n",
      "|1737804352468426752|1737804352195797004|    科技|  0.396503687134306|    科技|[4.19521558796986...|\n",
      "|1737804352468426752|1737804352195797004|    智慧|0.40752395034631195|    智慧|[-0.0046410071663...|\n",
      "|1737804352468426752|1737804352195797004|    卫玺| 1.5446951447568074|    卫玺|[0.00299180345609...|\n",
      "|1737804352468426752|1737804352195797004|    生态|  0.423822260084389|    生态|[-0.0019261098932...|\n",
      "|1737804352468426752|1737804352195797004|    智能| 1.0116009116784799|    智能|[-0.0059811612591...|\n",
      "|1737804352468426752|1737804352195797004|    体验|0.37849390833073765|    体验|[-0.0057077198289...|\n",
      "|1737804352468426752|1737804352195797004|    产品| 0.3510472579318176|    产品|[-0.0083605144172...|\n",
      "|1737804352468426752|1737804352195797004|    双手|0.09759499343769899|    双手|[0.00323853059671...|\n",
      "|1737804352468426752|1737804352195797004|    语音|  1.044585328060453|    语音|[-0.0027449589688...|\n",
      "|1737804352468426752|1737804352195797004|人工智能|0.48427433207721093|人工智能|[0.00344863743521...|\n",
      "|1737804352468426752|1737804352195797004|    家庭|0.36565310178194116|    家庭|[-0.0046691512688...|\n",
      "|1737804352468426752|1737804352195797004|  马桶盖| 1.5555519592661153|  马桶盖|[0.00453051738440...|\n",
      "|1737804352485203968|1737804352195797004|     com| 0.1985088754482789|     com|[0.00350727955810...|\n",
      "|1737804352485203968|1737804352195797004|    海报| 0.1262158823296421|    海报|[0.03638158366084...|\n",
      "|1737804352485203968|1737804352195797004|    艺术| 0.1970948276733118|    艺术|[0.00206391001120...|\n",
      "|1737804352485203968|1737804352195797004|    官方|0.12528553802837472|    官方|[0.00468378001824...|\n",
      "|1737804352485203968|1737804352195797004|  吴亦凡| 0.3984905309479861|  吴亦凡|[0.00817491579800...|\n",
      "|1737804352485203968|1737804352195797004|  发布会|0.12528553802837472|  发布会|[0.00959966797381...|\n",
      "|1737804352485203968|1737804352195797004|    小米| 1.0116009116784799|    小米|[0.08670850843191...|\n",
      "|1737804352485203968|1737804352195797004|    雷军|0.33214385988308637|    雷军|[0.00983773544430...|\n",
      "+-------------------+-------------------+--------+-------------------+--------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "article_keyword_vec_weights.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这里用词的权重 * 词的向量\n",
    "articleKeywordVectors = article_keyword_vec_weights.rdd.map(lambda row: (row.article_id, row.channel_id, row.keyword, row.weight * row.vector)).toDF([\"article_id\", \"channel_id\", \"keyword\", \"weightingVector\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# **计算得到文章的平均词向量即文章的向量**\n",
    "def avg(row):\n",
    "    x = 0\n",
    "    for v in row.vectors:\n",
    "        x += v\n",
    "    #  将平均向量作为article的向量\n",
    "    return row.article_id, row.channel_id, x / len(row.vectors)\n",
    "\n",
    "article_keyword_vec_weights.registerTempTable(\"tempTable\")\n",
    "articleVector = oa.spark.sql(\n",
    "    \"select article_id, min(channel_id) channel_id, collect_set(vector) vectors from tempTable group by article_id\").rdd.map(\n",
    "    avg).toDF([\"article_id\", \"channel_id\", \"article_vector\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+--------------------+\n",
      "|         article_id|         channel_id|      article_vector|\n",
      "+-------------------+-------------------+--------------------+\n",
      "|1737804352468426752|1737804352195797004|[-0.0016168010309...|\n",
      "|1737804352485203968|1737804352195797004|[0.02580031087725...|\n",
      "+-------------------+-------------------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "articleVector.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "def toArray(row):\n",
    "    return row.article_id, row.channel_id, [float(i) for i in row.article_vector.toArray()]\n",
    "\n",
    "articleVector = articleVector.rdd.map(toArray).toDF(['article_id', 'channel_id', 'article_vector'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 保存数据到hive\n",
    "articleVector.write.insertInto(\"article_vector\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1、计算相似度，先读取数据（保存到当前向量表中），进行类型处理\n",
    "article_vector = oa.spark.sql(\"select article_id, article_vector from article_vector limit 10\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[article_id: bigint, article_vector: array<float>]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_vector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.ml.linalg import Vectors\n",
    "def _array_to_vector(row):\n",
    "    return row.article_id, Vectors.dense(row.article_vector)\n",
    "\n",
    "train = article_vector.rdd.map(_array_to_vector).toDF(['article_id', 'article_vector'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[article_id: bigint, article_vector: vector]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2、用BRP（BucketedRandomProjectLSH）进行训练\n",
    "from pyspark.ml.feature import BucketedRandomProjectionLSH\n",
    "\n",
    "BRP = BucketedRandomProjectionLSH(inputCol='article_vector', outputCol='hashes', numHashTables=4.0, bucketLength=10.0)\n",
    "model = BRP.fit(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 240:>                (0 + 1) / 1][Stage 241:>                (0 + 0) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-------------------+\n",
      "|            datasetA|            datasetB|  EuclideanDistance|\n",
      "+--------------------+--------------------+-------------------+\n",
      "|{1737804352485203...|{1737804352485203...|                0.0|\n",
      "|{1737804352468426...|{1737804352468426...|                0.0|\n",
      "|{1737804352468426...|{1737804352485203...|0.30482734436967246|\n",
      "|{1737804352485203...|{1737804352468426...|0.30482734436967246|\n",
      "+--------------------+--------------------+-------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# 3、输入测试数据，给到相似的结果\n",
    "similar = model.approxSimilarityJoin(train, train, 2.0, distCol='EuclideanDistance')\n",
    "similar.sort(['EuclideanDistance']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_hbase(partition):\n",
    "    import happybase\n",
    "    pool = happybase.ConnectionPool(size=3, host='node1')\n",
    "\n",
    "    with pool.connection() as conn:\n",
    "        # 建立表的连接\n",
    "        table = conn.table('article_similar')\n",
    "        for row in partition:\n",
    "            if row.datasetA.article_id == row.datasetB.article_id:\n",
    "                pass\n",
    "            else:\n",
    "                table.put(str(row.datasetA.article_id).encode(),\n",
    "                         {\"similar:{}\".format(row.datasetB.article_id).encode(): b'%0.4f' % (row.EuclideanDistance)})\n",
    "        # 手动关闭所有的连接\n",
    "        conn.close()\n",
    "\n",
    "similar.foreachPartition(save_hbase)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "toutiao",
   "language": "python",
   "name": "toutiao"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
